package jdbc;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DriverTest {

	
	
	public static void main(String[] args) throws Exception {
		for(int i=0;true;i++){
			main_(args);
			System.err.println("次数："+i);
			Thread.sleep(10000);
		}
	}
	/**
	 * @param args
	 * @throws SQLException 
	 * @throws NullPointerException 
	 */
	public static void main_(String[] args) throws Exception {
		String host=System.getProperty("url","jdbc:laud://192.168.3.3:9170?data_port=9091&support_function=true&hive=true");
		Class.forName("cn.edu.thu.laud.jdbc.LaUDDriver");
		Connection con = DriverManager.getConnection(host, "", "");
		Statement statement=con.createStatement();
//		System.err.println("------------------------------------------begin--------------------------------------------");
//		try{
//			statement.execute("drop keyspace hxd_testks5");
//		}catch(Exception e){
//
//		}
//		ResultSet rSet;
//		System.err.println("------------------------------------------create keyspace, column family, index , and insert data (some use 'function' feature)--------------------------------------------");
//		statement.execute("create keyspace  hxd_testks5 with  strategy_class ='org.apache.cassandra.locator.SimpleStrategy' and strategy_options:replication_factor=1;");
//		statement.execute("use hxd_testks5");
//		statement.execute("create columnfamily user1 (id varchar primary key,name varchar,photo blob,sphoto 'sblob',hphoto 'hblob')");
//		statement.execute("create index on user1(name) "
//								+"with 'class_name'='cn.edu.thu.laud.lasql.index.LaUDIndex'" 
//								+" and 'implemention_class'='cn.edu.thu.laud.lasql.index.iface.impl.test.TestIndex'"
//								//+"and 'implemention_class'='cn.thu.thss.laud.index.solr.LaudSolrIndex'"
//								+" and 'test'='true'"
//				+"");
//		statement.execute("insert into user1(id,name,age) values(1,'黄向东',22)");
//		statement.execute("insert into user1(id,name,age) values('fffffffffffffffff','黄向东1',23)");
//		statement.execute("insert into user1(id,name,age) values(3,'清华大学2',22)");
//		statement.execute("insert into user1(id,name,age) values(4,'清华大学',22)");
//		statement.execute("insert into user1(id,name,age) values(5,'黄向东',225)");
//		statement.execute("insert into user1(id,name,age) values(6,'黄向东',236)");
//		statement.execute("insert into user1(id,name,age) values(7,'黄向东2',227)");
//		statement.execute("insert into user1(id,name,age) values(8,'黄向东3',228)");
//		//function
//		statement.execute("insert into user1(id,name) values(5,function('cn.edu.thu.laud.lasql.function.iface.impl.test.TestFunction','测试人员1','ceshi2'))");
//
////		try{
////			//不允许insert select update中使用remote函数
////			statement.execute("insert into user1(id,name) values(5,function('cn.edu.thu.laud.lasql.function.iface.impl.test.TestFunction','测试人员1','ceshi2') remote )");
////		}catch (Exception e) {
////			System.err.println(e.getMessage());
////			e.printStackTrace();
////		}
//		
//		try{
//			statement.execute("exec function('cn.edu.thu.laud.lasql.function.iface.impl.test.TestFunction','测试人员1','ceshi2') remote");
//		}catch (Exception e) {
//			System.err.println(e.getMessage());
//		}
//		
//		
//		System.err.println("------------------------------------------sblob upload--------------------------------------------");
//		//sblob upload
//		statement.execute("insert into user1(id,name,age,sphoto) values(5,'黄向东',24,'G:/test/1.jpg')");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
//			rSet.updateBlob("sphoto",System.in);
//		}
//		rSet.close();
//		System.err.println("------------------------------------------sblob download--------------------------------------------");
//		//sblob download
//		statement.execute("select sphoto from user1 ");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
//			Blob blob=rSet.getBlob("sphoto");
//			if(blob!=null){
//				InputStream iStream=blob.getBinaryStream();
//				writeIntoFile(iStream, "G:/test/a.jpg");
//			}
//		}
//		rSet.close();
////		
//		System.err.println("------------------------------------------hblob upload--------------------------------------------");
//		//hblob upload
//		statement.execute("insert into user1(id,hphoto) values (6,'G:/test/6.jpg')"); //,'F:/lib/laud-thrift-1.0.7-1.jar'
//		ResultSet rsResultSet = statement.getResultSet();
//		while(rsResultSet.next()){
//			rsResultSet.updateBlob("hphoto", System.in);
//		}
//		rsResultSet.close();
//		System.err.println("------------------------------------------hblob download--------------------------------------------");
//		//hblob download
//		statement.execute("select hphoto from user1 ");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
//			((FileTransfor)rSet).getBlob("hphoto","G:/test/b.jpg");
//		}
//		rSet.close();
//		
//////		statement.execute("select id,name,age,photo from user1 where name='黄向东' and age <23");
//////		rSet=statement.getResultSet();
//////		while(rSet.next()){
//////			System.out.println(rSet.getObject("id"));
//////		}
////
////		statement.execute("select * from user1 where name=function('cn.edu.thu.laud.lasql.function.iface.impl.test.TestFunction','测试人员1','ceshi2')");
////
//		System.err.println("------------------------------------------select by a fake index--------------------------------------------");
//		statement.execute("select name,id from user1 where name='清华大学' limit 20");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
////			System.out.println(rSet.getObject(3));
//		//	System.out.println(rSet.getObject(2));
//			System.out.println(rSet.getObject(1));
//			//System.out.println(rSet.getObject(2));
//			
//		}
//		Thread.sleep(2000);
//		System.err.println("------------------------------------------select without index--------------------------------------------");
//		statement.execute("select name,id from user1 limit 20");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
////			System.out.println(rSet.getObject(3));
//			System.out.println(rSet.getObject(2));
//			System.out.println(rSet.getObject(1));
//			//System.out.println(rSet.getObject(2));
//			
//		}
		System.err.println("------------------------------------------hive test--------------------------------------------");
		//hive test
		statement.execute("use default with olap");
		Thread.sleep(2000);
		try{
			statement.execute("drop table tbtest1 with olap");
			Thread.sleep(3000);
		}catch(Exception e){
			
		}
		try{
			statement.execute("drop table tbtest2 with olap");
			Thread.sleep(3000);

		}catch(Exception e){
			
		}
		System.err.println("------------------------------------------hive create table--------------------------------------------");
		try{
		statement.execute("create table tbtest1(id int,name string) with olap");
		Thread.sleep(3000);

		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		try{
		statement.execute("create table tbtest2(id int,age int) with olap");
		Thread.sleep(3000);

		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		System.err.println("------------------------------------------hive show tables' name--------------------------------------------");
		statement.execute("show tables with olap");
		ResultSet rs=statement.getResultSet();
		while(rs.next()){
			System.out.println(rs.getObject(1));
		}
		statement.close();

		System.err.println("-------------------close-------------------------");
		con.close();
	}

	private static  void writeIntoFile(InputStream instream,String filePath) throws IOException, SQLException{
		File file=new File(filePath);

		if(file.isDirectory()){
			throw new SQLException("file not folder..");
		}
		BufferedInputStream in = new BufferedInputStream(instream);        
		FileOutputStream out = new FileOutputStream(file);        

		//  System.out.println("Available bytes:" + in.available());        

		byte[] temp = new byte[4096];        
		int size = 0;        
		try{
			while ((size = in.read(temp)) != -1) {        
				out.write(temp, 0, size);        
			}
		}catch (IOException e) {
			throw e;
		}
		finally{
			try {
				in.close();
			} catch (IOException e) {
				in=null;
			}        
		}
		in.close();
		out.close();
	}
}
